"""
0.安装pandas(pip install pandas)
1.pandas 读取xlsx文件列表(DataFrame)
2.DataFrame记录转存数据库
"""
import sqlite3
import pandas as pd

def excel_to_table(excel_file,tbl_name):
    """
    将excel导入sqlite
    """
    # 异常处理机制
    try:
    # 读取数据
        df = pd.read_excel(excel_file)

        # print(df.head()) # 查看前五条记录
        # print(df.shape) # 表结构查看
        # print(df.columns) # 表列名

        # 转存数据库
        df.to_sql(name=tbl_name,con='sqlite:///data1.db')
        return {'info':'info','content':f'数据库{tbl_name}导入成功！'}

    except:
        return {'info':'error','content':f'数据文件{excel_file}导入失败！'}
    
def drop_table(tbl_name,db_file='data1.db'):
    """
    查询sqlite数据库中指定名称的表
    """
    conn = sqlite3.connect(db_file)
    # 创建cursor访问表
    cursor = conn.cursor()
    cursor.execute(f'drop table {tbl_name}')
    conn.commit()

def get_table_names(db_file='data1.db'):
    """
    查询sqlite数据库中所有的表名
    """
    conn = sqlite3.connect(db_file)
    # 创建cursor访问表
    cursor = conn.cursor()
    cursor.execute('select tbl_name from sqlite_master')
    tbl_names= cursor.fetchall()
    return list(set([tbl[0] for tbl in tbl_names]))


if __name__=='__main__':
    # info = excel_to_table('data_files/2020sd_rank.xlsx','sd_rank_2020')

    # print(info)

    print(get_table_names())